﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Windows.Forms;

namespace TunnelManager.Tools
{
    /// <summary>
    /// SQL Server操作封装类，提供多个方便实用的数据库操作方法，该类的对象需要实例化。
    /// </summary>
    class SqlDBHelper
    {
        //public string connectionString = ConfigurationManager.ConnectionStrings["Tunnel.Properties.Settings.TunnelXAT1ConnectionString"].ToString();
        public string connectionString = TunnelManager.Properties.Settings.Default.TunnelXAT1ConnectionString;
        private SqlConnection connection;
        /// <summary>
        /// 数据库连接
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                try
                {
                    if (connection == null)
                    {
                        connection = new SqlConnection(connectionString);

                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    else if (connection.State == System.Data.ConnectionState.Broken)
                    {
                        connection.Close();
                        connection.Open();
                    }
                }
                catch (SqlException sExcp)
                {
                    connection = null;
                    MessageBox.Show("数据库连接失败，请检查数据库连接：" + sExcp.Number.ToString(), "数据库连接错误", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                }

                return connection;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseDb()
        {
            if ((connection != null) && (connection.State != ConnectionState.Closed))
            {
                connection.Close();
                connection = null;
            }
        }


        /// <summary>
        /// 执行一个带有数个参数的SQL语句，完成后返回受影响行数
        /// </summary>
        /// <param name="sql">执行的SQL语句</param>
        /// <param name="values">SQL参数数组</param>
        /// <returns>返回受影响行数</returns> 
        public int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int iCount = cmd.ExecuteNonQuery();
            CloseDb();
            return iCount;
        }
        /// <summary>
        /// 执行一个的SQL语句，完成后返回受影响行数
        /// </summary>
        /// <param name="sql">执行的SQL语句</param>        
        /// <returns>返回受影响行数</returns> 
        public int ExecuteCommand(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);

            int iCount = cmd.ExecuteNonQuery();
            CloseDb();
            return iCount;

        }

        /// <summary>
        /// 将一组SQL语句以事务方式运行
        /// </summary>
        /// <param name="sqlstrings">SQL语句字符串数组</param>
        /// <returns>所有SQL事务受影响行数总和</returns>        
        public int ExecteTranCommand(string[] sqlstrings)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            SqlTransaction sTran = Connection.BeginTransaction();
            cmd.Transaction = sTran;
            try
            {
                int iCount = 0;
                foreach (string sql in sqlstrings)
                {
                    if (sql.Trim().Length > 1)
                    {
                        cmd.CommandText = sql;
                        iCount += cmd.ExecuteNonQuery();
                    }
                }
                sTran.Commit();
                return iCount;
            }
            catch (SqlException sExcp)
            {
                sTran.Rollback();
                MessageBox.Show("执行数据库事务出现错误，请检查数据库连接，错误代码为：" + sExcp.Number.ToString(), "事务运行错误", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1, MessageBoxOptions.ServiceNotification);
                return 0;
            }
        }

        /// <summary>
        /// 执行SQL语句并获得一个DataSet
        /// </summary>
        /// <param name="sql">被执行的SQL语句</param>
        /// <returns>返回一个DataSet</returns>
        public DataSet GetSet(string sql)
        {

            SqlCommand cmd = new SqlCommand(sql, Connection);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            CloseDb();
            return ds;

        }
        /// <summary>
        /// 执行SQL语句并获得一个DataTable
        /// </summary>
        /// <param name="sql">被执行的SQL语句</param>
        /// <returns>返回一个DataTable</returns>
        public DataTable GetTable(string sql)
        {

            SqlCommand cmd = new SqlCommand(sql, Connection);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);
            CloseDb();
            return dt;

        }

        /// <summary>
        /// 执行一个带有参数SQL存储过程，并且返回一个DataTable
        /// </summary>
        /// <param name="StoredProcedureName">数据库中的存储过程名称</param>
        /// <param name="pares">存储过程需要的参数</param>
        /// <returns>返回一个DataTable</returns>
        public DataTable GetTable(string StoredProcedureName, params SqlParameter[] pares)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(StoredProcedureName, Connection);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (pares != null)
            {
                foreach (SqlParameter pare in pares)
                {
                    da.SelectCommand.Parameters.Add(pare);
                }
            }
            da.Fill(ds);
            return ds.Tables[0];
        }

        public int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            CloseDb();
            return result;
        }
        public int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            CloseDb();
            return result;
        }
        public SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        public SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
    }
}

